<?php

namespace app\admin\controller;

use app\admin\common\Base;
use think\Db;
use think\Session;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class Goodstags extends Base
{
	//标签列表
	public function index()
	{
		$data = $this->request->param();
		if (isset($data['ajax']) && $data['ajax'] == 1) {
			$page = isset($data['page']) ? $data['page'] : 1;
			$page = intval($page);
			$limit = isset($data['rows']) ? $data['rows'] : 10;
			$limit = intval($limit);
			$start = $limit * ($page - 1);

			if (isset($data['search'])) {
				if ($data['tj'] == 0) {
					$list = Db::name('goodstags')->where('tags_name', 'like', '%' . $data['search'] . '%')->order('id desc')->limit($start, $limit)->select();
					$count = Db::name('goodstags')->where('tags_name', 'like', '%' . $data['search'] . '%')->order('id desc')->count();
				} else {
					$list = Db::name('goodstags')->where('count', $data['search'])->order('id desc')->limit($start, $limit)->select();
					$count = Db::name('goodstags')->where('count', $data['search'])->order('id desc')->count();
				}
			} else {
				$list = Db::name('goodstags')->order('id desc')->limit($start, $limit)->select();
				$count = Db::name('goodstags')->order('id desc')->count();
			}

			foreach ($list as $key => $value) {
				switch ($value['type']) {
					case 0:
						$list[$key]['type_name'] = '台';
						break;
				}
				$list[$key]['name'] = Db::name('good')->where('id', $value['good_id'])->find()['name'];
				$list[$key]['addtime'] = date('Y-m-d H:i:s', $value['addtime']);

				if ($value['active_status'] == 0) {
					$list[$key]['active_status'] = '未激活';
				}

				if ($value['active_status'] == 1) {
					$list[$key]['active_status'] = '已激活';
				}

				if ($value['active_status'] == 2) {
					$list[$key]['active_status'] = '已作废';
				}
				// 统计未导入序列号的标签
				$list[$key]['sn_count'] = Db::name('tagslogs')->where('goodstags_id', $value['id'])->where('sn', '')->count();
			}
			return $this->json_data($count, $list);
		}
		return $this->fetch();
	}

	public function add()
	{
		$good_id = input('id');
		$this->assign('good_id', $good_id);
		return $this->fetch();
	}

	//编辑栏目1-2
	public function save()
	{

		$status = 0;
		$msg = "操作失败";
		$data = $this->request->param();

		if ($data['count'] <= 0) {
			$msg .= "，生成数量不能为0";
			return ['status' => $status, 'msg' => $msg];
		}

		if ($data['count'] > 100000) {
			$msg .= "，生成数量不能大于10万";
			return ['status' => $status, 'msg' => $msg];
		}

		$good = Db::name('good')->where('id', $data['good_id'])->find();

		$data['addtime'] = time();
		// 启动事务
		Db::startTrans();
		//执行操作
		$countArr = [];
		$data['tags_name'] = $good['name'];
		$tagsid = Db::table('goodstags')->insertGetId($data);
		for ($i = 0; $i < $data['count']; $i++) {
			$temp['goodstags_id'] = $tagsid;
			$temp['status'] = 0;
			array_push($countArr, $temp);
		}

		$res = Db::table('tagslogs')->insertAll($countArr);
		// 获取标签明细数据
		$tagslist = Db::name('tagslogs')->where('goodstags_id', $tagsid)->select();
		// 生成文件
		//$urlStr = "http://verification.vimap.cn?id=";
		$sinfo=Db::name('system')->where('id',1)->find();
		$urlStr = $sinfo['link']."mobile/login/index.html?id=";
		$urlArr = [];
		foreach ($tagslist as $key => $value) {
			$temp = $urlStr . $value['id'] . PHP_EOL;
			array_push($urlArr, $temp);
		}
		$urls = implode('', $urlArr);

		$tagPath = ROOT_PATH . '/uploads/tags/' . $tagsid;
		$this->directory($tagPath);
		//chmod($tagPath, 0777);
		$filePath = $tagPath . '/' . $good['name'];
		file_put_contents($filePath . '.txt', $urls);
		file_put_contents($filePath . '.csv', $urls);

		// 生成zip压缩包
		new Makezip($tagPath, $tagPath . '/' . $good['name'] . '.zip');

		if ($res && $tagsid) {
			// 提交事务
			Db::commit();
			$status = 1;
			$msg = "添加成功";
		} else {
			// 回滚事务
			Db::rollback();
		}
		adminlog("标签生成：{$good['name']}，{$data['count']} 个", '标签生成');
		return ['status' => $status, 'msg' => $msg];
	}

	//递归式创建目录
	public function directory($dir)
	{
		return is_dir($dir) or self::directory(dirname($dir)) and mkdir($dir, 0777);
	}

	//删除栏目1-1
	public function del($id)
	{
		$status = 0;
		$msg = "操作失败";
		if (empty($id)) {
			$msg .= '，ID不存在';
		} else {
			$del = Db::name('goodstags')->where('id', $id)->delete();
			if ($del) {
				adminlog("标签删除", '删除');
				$status = 1;
				$msg = "删除成功";
			}
		}

		return ['status' => $status, 'msg' => $msg];
	}

	// 标签激活
	public function active()
	{
		return $this->fetch();
	}
	// 激活
	public function jh()
	{
		$data = $this->request->param();
		$arr = [];
		foreach ($data as $key => $value) {
			if ($key == 'status') {
				break;
			}
			array_push($arr, explode('=', $value)[1]);
		}
		$tags = Db::name('tagslogs')->where('id', 'in', $arr)->select();
		$tags_id = [];
		foreach ($tags as $key => $value) {
			array_push($tags_id, $value['goodstags_id']);
		}
		if (count(array_unique($tags_id)) > 1) {
			return json(['status' => 0, 'msg' => '只能同一批次的标签']);
		}
		$res = Db::name('goodstags')->where('id', $tags_id[0])->update(['active_status' => $data['status']]);
		if ($res) {
			Db::name('activelogs')->insert(['goodstags_id' => $tags_id[0], 'addtime' => time(), 'admin_id' => Session::get('user_id'), 'type' => $data['status']]);
			adminlog("标签激活", '激活');
			return json(['status' => 1, 'msg' => '成功']);
		} else {
			return json(['status' => 0, 'msg' => '失败']);
		}
	}

	// 激活记录
	public function activelogs()
	{
		$data = $this->request->param();
		if (isset($data['ajax']) && $data['ajax'] == 1) {
			$map = '';
			$page = isset($data['page']) ? $data['page'] : 1;
			$page = intval($page);
			$limit = isset($data['rows']) ? $data['rows'] : 10;
			$limit = intval($limit);
			$start = $limit * ($page - 1);

			if ($map == '') {
				$list = Db::name('activelogs')->order('id desc')->limit($start, $limit)->select();
				$count = Db::name('activelogs')->count();
			} else {
				$list = Db::name('activelogs')->order('id desc')->limit($start, $limit)->select();
				$count = Db::name('activelogs')->count();
			}

			foreach ($list as $key => $value) {
				$list[$key]['addtime'] = date('Y-m-d H:i:s', $value['addtime']);
				$list[$key]['admin'] = Db::name('admin')->where('id', $value['admin_id'])->value('username');
				$goodstags = Db::name('goodstags')->where('id', $value['goodstags_id'])->find();
				$good = Db::name('good')->where('id', $goodstags['good_id'])->find();
				$list[$key]['good'] = $good['name'];
				$list[$key]['number'] = $goodstags['count'];
				if ($list[$key]['type'] == 1) {
					$list[$key]['type_name'] = '激活';
				} else {
					$list[$key]['type_name'] = '作废';
				}
			}
			return $this->json_data($count, $list);
		}
		return $this->fetch();
	}


	public function import_sn()
	{
		$goodstags_id = input('id');
		$this->assign('goodstags_id', $goodstags_id);
		return $this->fetch();
	}

	//保存序列号导入
	public function save_import()
	{

		$status = 0;
		$msg = "操作失败";
		$data = $this->request->param();

		if (empty($data['sn'])) {
			return ['status' => $status, 'msg' => '请上传文件'];
		}

		$file_name = ROOT_PATH . 'uploads' . DS . 'tagslogs_sn' . DS . $data['sn'];
		$fileExtendName = substr(strrchr($data['sn'], '.'), 1);
		// 判断文件后缀名
		if ($fileExtendName == 'xlsx') {
			$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
			$spreadsheet = $reader->load($file_name);
		} else if ($fileExtendName == 'xls') {
			$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
			$spreadsheet = $reader->load($file_name);
		}
		//excel中的第一张sheet
		$sheet = $spreadsheet->getSheet(0);
		// 取得总行数
		$highestRow = $sheet->getHighestRow();
		// 获取导入的条数
		$import_count = $highestRow - 1;
		// 判断行数是否大于剩余导入的数量
		$countsn = Db::name('tagslogs')->where(['goodstags_id' => $data['goodstags_id'], 'sn' => ''])->count();
		if ($countsn < $import_count) {
			return ['status' => $status, 'msg' => '导入的数量不能大于剩余序列号数量'];
		}
		// $highestColumn = $sheet->getHighestColumn();   // 取得总列数
		$sn_arr = [];
		// 查询所有已经导入的序列号
		$snListArr = Db::name('tagslogs')->field('sn')->where(['goodstags_id' => $data['goodstags_id']])->where('sn', '<>', '')->select();
		$snList = array_to_arr($snListArr, 'sn');
		// 启动事务
		Db::startTrans();
		$more_sn=[];//多序列号匹配表
		for ($j = 2; $j <= $highestRow; $j++) {
			$sn = $spreadsheet->getActiveSheet()->getCell("A" . $j)->getValue();
			if(empty($sn)){//保证sn存在

			}else{
				
				$intime = $spreadsheet->getActiveSheet()->getCell("B" . $j)->getValue();
				$outime = $spreadsheet->getActiveSheet()->getCell("C" . $j)->getValue();
				if (in_array($sn, $snList)) {
					// 回滚事务
    				Db::rollback();
					return ['status' => $status, 'msg' => '序列号' . $sn . '重复导入'];
				}
				if (!empty($intime)) {
					if (is_numeric($intime)) {
						$intime = date('Y-m-d', ($intime - 25569) * 24 * 3600);
					}
				}

				if (!empty($outime)) {
					if (is_numeric($outime)) {
						$outime = date('Y-m-d', ($outime - 25569) * 24 * 3600);
					}
				}
				
				$temp['intime'] = empty($intime) ? 0 : strtotime($intime);
				$temp['outtime'] = empty($outime) ? 0 : strtotime($outime);
				$temp['snkey'] = 0; //关联号标识符
				
				$sn=str_replace('，',',', $sn);//若存在中文逗号，将中文逗号换为英文逗号
				$cstr=checkstr($sn,',');//判断是否是多个序列码-是否存在英文逗号
				if($cstr){//包含--多个序列码-包含英文逗号
					$sns_arr=strtoarr($sn);
					foreach ($sns_arr as $ksn => $vsn) {
						if(empty($vsn)){
							// 回滚事务
							Db::rollback();
							return ['status' => $status, 'msg' => '多个序列号格式不对'];
						}
						if (in_array($vsn, $snList)) {
							// 回滚事务
							Db::rollback();
							return ['status' => $status, 'msg' => '序列号' . $vsn . '重复导入'];
						}
						$temp['sn'] = $vsn;
						if($ksn != 0){
							$temp['snkey'] = 1; //关联号标识符-非第一个便变更标识符
						}
						array_push($sn_arr, $temp);
					}
					$ins=[
						'key'=>$sn,
					];
					//halt($sns_arr);
					$oldSns = $sns_arr;
					$newSns = array_unique($oldSns);
					if (count($oldSns) != count($newSns)) {
						// 回滚事务
						Db::rollback();
						return ['status' => $status, 'msg' => '导入的序列号存在重复'];
					}

					Db::name('more_sn')->insert($ins);
					
				}else{//不包含
					$temp['sn'] = $sn;
					array_push($sn_arr, $temp);	
				}
				

			}
			
		}
		//halt($sn_arr);
		
		$oldSn = array_to_arr($sn_arr, 'sn');
		$newSn = array_unique($oldSn);
		if (count($oldSn) != count($newSn)) {
			// 回滚事务
			Db::rollback();
			return ['status' => $status, 'msg' => '导入的序列号存在重复'];
		}

		if ($countsn <count($oldSn)) {
			// 回滚事务
			Db::rollback();
			return ['status' => $status, 'msg' => '导入的数量不能大于剩余序列号数量'];
		}
		//echo($highestRow - 1);
		//$tagslogs = Db::name('tagslogs')->where(['goodstags_id' => $data['goodstags_id'], 'sn' => ''])->limit(0, $highestRow - 1)->select();
		$tagslogs = Db::name('tagslogs')->where(['goodstags_id' => $data['goodstags_id'], 'sn' => ''])->limit(0, count($sn_arr))->select();
		// $updateSql = "UPDATE tagslogs SET sn = CASE id WHEN 1 THEN 'sn_1' WHEN 2 THEN 'sn_2' WHEN 3 THEN 'sn_3' END, addtime = CASE id WHEN 1 THEN 1  WHERE id IN (1,2,3)";
		$updateSql = ["UPDATE tagslogs SET sn = CASE id"];
		$idArr = [];
		$outArr = [];
		$inArr = [];
		$updatetimeArr = [];
		$keyArr = [];//关联号标识符
		foreach ($tagslogs as $key => $value) {
			// 导入序列号
			$idSql = " WHEN " . $value['id'];
			$snSql = " THEN " . "'" . $sn_arr[$key]['sn'] . "'";
			$tempSql = $idSql . $snSql;

			array_push($updateSql, $tempSql);
			array_push($idArr, $value['id']);

			// 导入出库日期
			$outSql = " THEN " . "'" . $sn_arr[$key]['outtime'] . "'";
			$tempOutSql = $idSql . $outSql;
			array_push($outArr, $tempOutSql);

			// 导入入库日期
			$inSql = " THEN " . "'" . $sn_arr[$key]['intime'] . "'";
			$tempInSql = $idSql . $inSql;
			array_push($inArr, $tempInSql);

			// 更新时间
			$updatetimeSql = " THEN ". time();
			$tempupdateSql = $idSql . $updatetimeSql;
			array_push($updatetimeArr, $tempupdateSql);
			// 关联号标识符
			$keySql = " THEN " . "'" . $sn_arr[$key]['snkey'] . "'";
			$tempkeySql = $idSql . $keySql;
			array_push($keyArr, $tempkeySql);
		}

		// 出库字符串
		$endSql1 = " END, outtime = CASE id";
		array_push($updateSql, $endSql1);
		// 出库数组
		$outSqls = implode('', $outArr);
		array_push($updateSql, $outSqls);

		// 入库字符串
		$endSql2 = " END, addtime = CASE id";
		array_push($updateSql, $endSql2);
		// 入库数组
		$inSqls = implode('', $inArr);
		array_push($updateSql, $inSqls);

		// 更新时间字符串
		$endSql3 = " END, updatetime = CASE id";
		array_push($updateSql, $endSql3);
		// 更新数组
		$inSqls = implode('', $updatetimeArr);
		array_push($updateSql, $inSqls);

		//关联号标识符字符串
		$endSql4 = " END, snkey = CASE id";
		array_push($updateSql, $endSql4);
		// 关联号标识符数组
		$keySqls = implode('', $keyArr);
		array_push($updateSql, $keySqls);



	
		$endSql = " END WHERE id IN (";
		array_push($updateSql, $endSql);

		$idStr = implode(',', $idArr);
		array_push($updateSql, $idStr);

		array_push($updateSql, ")");

		$sql = implode('', $updateSql);
		
		$rest = Db::execute($sql);
		if ($rest) {
			// 提交事务
			Db::commit();
			$status = 1;
			$msg = "导入成功";
		}

		return ['status' => $status, 'msg' => $msg];
	}

	public function daochu()
	{
		$data = $this->request->param();
		if (!empty($data['stime'])) {
			$data['stime'] = strtotime($data['stime'] . " 00:00:00");
			$data['etime'] = strtotime($data['etime'] . " 23:59:59");
			$lists = Db::name('goodstags')->order('id desc')->where('addtime', '>=', $data['stime'])->where('addtime', '<=', $data['etime'])->select();
		} else {
			$lists = Db::name('goodstags')->order('id desc')->select();
		}

		foreach ($lists as $key => $value) {
			$lists[$key]['importnum'] = Db::name('tagslogs')->where('goodstags_id', $value['id'])->where('sn', '<>', '')->count();
			$lists[$key]['addtime'] = $value['addtime'] == 0 ? '' : date('Y-m-d H:i:s');
		}

		$spreadsheet = new Spreadsheet();
		$PHPSheet = $spreadsheet->getActiveSheet();

		$PHPSheet->setTitle("标签下载"); //给当前活动sheet设置名称
		$PHPSheet->setCellValue("A1", "商品名称")
			->setCellValue("B1", "标签生成数量")
			->setCellValue("C1", "未导入序列号数量")
			->setCellValue("D1", "添加时间");
		$styleArray = [
			'alignment' => [
				'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
			],
		];
		foreach ($lists as $key => $val) {
			$spreadsheet->setActiveSheetIndex(0)
				->setCellValue("A" . ($key + 2), $val['tags_name'])
				->setCellValue("B" . ($key + 2), $val['count'])
				->setCellValue("C" . ($key + 2), $val['count'] - $val['importnum'])
				->setCellValue("D" . ($key + 2), $val['addtime']);

			$spreadsheet->setActiveSheetIndex(0)->getStyle('A' . ($key + 2))->applyFromArray($styleArray);
		}

		$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(30);

		$time = date('Y-m-d', time());
		// Redirect output to a client’s web browser (Xlsx)
		header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		header('Content-Disposition: attachment;filename="标签下载' . $time . '.xlsx"');
		header('Cache-Control: max-age=0');
		// If you're serving to IE 9, then the following may be needed
		header('Cache-Control: max-age=1');
		// If you're serving to IE over SSL, then the following may be needed
		header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
		header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
		header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
		header('Pragma: public'); // HTTP/1.0

		$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
		$writer->save('php://output');
	}
}
